import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class ComplementarySampleColumnApp {

	public static void main(String[] args) throws IOException, SQLException,
			ClassNotFoundException {

		// Load the JDBC driver
		String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC
		// driver
		Class.forName(driverName);

		// Create a connection to the database
		String serverName = "zhoudb.usc.edu";
		String mydatabase = "seq_db";
		String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
		// JDBC
		// url
		String username = "siyuanh";
		String password = "11";
		Connection connection = DriverManager.getConnection(url, username,
				password);
		Statement stat = connection.createStatement();

		InputStream inp = ClassLoader.getSystemResourceAsStream("iReg_db.xls");
		HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
		Sheet sheet = wb.getSheet("SRA");
		for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
			Row row = rit.next();
			String sql = "update SAMPLE_TABLE SET CELL_TYPE = '"
					+ row.getCell(6).getStringCellValue() + "', FACTOR='"
					+ row.getCell(7).getStringCellValue()
					+ "' WHERE SAMPLE_ID='"
					+ row.getCell(3).getStringCellValue() + "'";
			stat.executeUpdate(sql);
		}
		connection.close();

	}
}
